import pymysql


def get_shixiseng_education():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT education, COUNT(*) as count FROM lagou_table GROUP BY education'
    cur.execute(sql)
    datas = cur.fetchall()
    companyName = [i['education'] for i in datas]
    counts = [i['count'] for i in datas]

    data2 = []
    for i in range(len(companyName)):
        data2.append({'name': companyName[i], 'value': counts[i]})
    data = {'data': data2}
    print(data)
    cur.close()
    conn.close()
    return data


def get_count_City():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT city, COUNT(*) as count FROM shixiseng_table GROUP BY city ORDER BY count desc LIMIT 10'
    cur.execute(sql)
    datas1 = cur.fetchall()
    city = [i['city'] for i in datas1]
    count = [i['count'] for i in datas1]
    data = {'city': city, 'count': count}
    print(data)
    cur.close()
    conn.close()
    return data


def get_shixiseng_WordCloud():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT CompanyBenefits FROM shixiseng_table'
    cur.execute(sql)
    datas = cur.fetchall()
    CompanyBenefits = [str(i['CompanyBenefits']).split('，') for i in datas]
    data1 = {}
    for i in CompanyBenefits:
        for j in i:
            if j in data1:
                data1[j] += 1
            else:
                data1[j] = 1
    data2 = []
    for key, value in data1.items():
        data2.append({'name': key, 'value': value})
    data = {'data': data2}
    print(data)
    cur.close()
    conn.close()
    return data


def get_sxs_industryField():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT industryField, COUNT(*) as count FROM shixiseng_table GROUP BY industryField'
    cur.execute(sql)
    datas = cur.fetchall()
    industryField = [i['industryField'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'industryField': industryField, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_shixiseng_salary():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT salary FROM shixiseng_table'
    cur.execute(sql)
    datas = cur.fetchall()
    salary = [i['salary'] for i in datas]
    ranges = [5, 10, 15, 20]
    counts = [0] * (len(ranges))  # 初始化数量数组
    salary_ranges = ['0k-5k', '5k-10k', '10k-15k', '15k-20k']
    for i in salary:
        max_salary = float(i.split('-')[1].replace('k', ''))
        for index, value in enumerate(ranges):
            if max_salary < value:
                counts[index] += 1
                break
    data2 = []
    for i in range(len(salary_ranges)):
        data2.append({'name': salary_ranges[i], 'value': counts[i]})
    data = {'data': data2}
    print(data)
    cur.close()
    conn.close()
    return data


def get_sxs_positionName():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT positionName, COUNT(*) as count FROM shixiseng_table GROUP BY positionName ORDER BY count desc LIMIT 15'
    cur.execute(sql)
    datas = cur.fetchall()
    positionName = [i['positionName'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'positionName': positionName, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_companySize():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT companySize, COUNT(*) as count FROM shixiseng_table GROUP BY companySize HAVING count > 100'
    cur.execute(sql)
    datas = cur.fetchall()
    companySize = [i['companySize'] for i in datas]
    counts = [i['count'] for i in datas]
    data2 = []
    for i in range(len(companySize)):
        data2.append({'name': companySize[i], 'value': counts[i]})
    data = {'data': data2}
    print(data)
    cur.close()
    conn.close()
    return data


def get_sxs_companyName():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT companyName, COUNT(*) as count FROM shixiseng_table GROUP BY companyName HAVING count > 50'
    cur.execute(sql)
    datas = cur.fetchall()
    companyName = [i['companyName'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'companyName': companyName, 'count': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_sxs_positionName_salary():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT salary, positionName FROM shixiseng_table LIMIT 5'
    cur.execute(sql)
    datas = cur.fetchall()
    salary = [str(i['salary']).split('·')[0] for i in datas]
    salary1 = []
    for i in salary:
        min = float(i.split('-')[0].replace('k', '')) * 1000
        max = float(i.split('-')[1].replace('k', '')) * 1000
        salary1.append(int((max + min) / 2))
    positionName = [i['positionName'] for i in datas]
    data = {'salary': salary1, 'positionName': positionName}
    print(data)
    cur.close()
    conn.close()
    return data


if __name__ == '__main__':
    get_sxs_positionName_salary()
